package com.manager.service.user; 

import java.io.Writer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import oracle.sql.CLOB;

import org.common.util.TranslateHelper;
import org.jorm.Session;

import com.build.SessionBuilder;  
import com.web.model.Userinfo; 
import com.web.model.dao.UserinfoDao; 
import com.web.model.dao.impl.UserinfoDaoImpl;
import com.web.model.vo.UserinfoVo;
import com.web.tag.bean.PageModel;

public class UserService {

	public List<Userinfo> findList(PageModel model,UserinfoVo query) {
		List<Userinfo> list = null;
		try {
			Session session = SessionBuilder.oracleSession();
			UserinfoDao userinfoDao = new UserinfoDaoImpl();
			String whereSql = " where 1=1 ";
			if(query.getUsername()!=null&&!query.getUsername().equals("")){
           	 
				whereSql=whereSql+" and "+Userinfo.COLUMN_USERNAME+" like '%"+query.getUsername()+"%' ";
            }
			if(query.getName()!=null&&!query.getName().equals("")){
	           	 
				whereSql=whereSql+" and "+Userinfo.COLUMN_NAME+" like '%"+query.getName()+"%' ";
            }
			if(query.getCompany()!=null&&!query.getCompany().equals("")){
	           	 
				whereSql=whereSql+" and "+Userinfo.COLUMN_COMPANY+" like '%"+query.getCompany()+"%' ";
            }
			if(query.getEmail()!=null&&!query.getEmail().equals("")){
	           	 
				whereSql=whereSql+" and "+Userinfo.COLUMN_EMAIL+" like '%"+query.getEmail()+"%' ";
            }
			if(query.getState()!=null&&query.getState()>0){
	           	 
				whereSql=whereSql+" and "+Userinfo.COLUMN_STATE+" ="+query.getEmail()+"  ";
            }
			String sql = "select " + Userinfo.COLUMN_ID + "," + Userinfo.COLUMN_ADDTIME
			+ "," + Userinfo.COLUMN_COMPANY+ "," + Userinfo.COLUMN_EMAIL + "," + Userinfo.COLUMN_NAME
			+ "," + Userinfo.COLUMN_PASS_ANS+ "," + Userinfo.COLUMN_PASS_PRO+ "," + Userinfo.COLUMN_PASSWORD
			+"," + Userinfo.COLUMN_STATE+ "," + Userinfo.COLUMN_TELEPHONE+ "," + Userinfo.COLUMN_USERNAME+ " from "
					+ Userinfo.TABLE_NAME + " " + whereSql + " order by " + Userinfo.COLUMN_ADDTIME+ " desc";
             
			if (model != null) {
				try {
					model.setRecodeZong(userinfoDao.getResultCount("select count(*) as ct from " + Userinfo.TABLE_NAME + " " + whereSql, 1).intValue());
				} catch (SQLException e) {
					e.printStackTrace();
				}
				// sql = sql + "limit " + model.getCountPage() *
				// (model.getPageNum() - 1) + "," + model.getCountPage();
				int row_x = model.getCountPage() * model.getPageNum();
				int row_y = (model.getPageNum() - 1) * model.getCountPage();
				sql = "select * from ( select row_.*, rownum rownum_ from (" + sql + ") row_ where rownum <= " + row_x + ") where rownum_ > " + row_y;
			}

			list =list(sql);

			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public void saveOrUpdate(Userinfo userinfo) {
	
		 
			if (userinfo.getId() == null || userinfo.getId() <= 0) {
				 save(userinfo);
			} else {
				 update(userinfo);
			} 
		 
	}

	 
	public List list(String sql) {
		List list=new ArrayList();
		Userinfo obj = null;
		try {
			Session session = SessionBuilder.oracleSession();
			Connection conn = session.connection(); 
			PreparedStatement ps = conn.prepareStatement(sql);
			 
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				obj = new Userinfo();
				obj.setAddtime(rs.getTimestamp(Userinfo.COLUMN_ADDTIME));
				obj.setCompany(rs.getString(Userinfo.COLUMN_COMPANY));
				obj.setEmail(rs.getString(Userinfo.COLUMN_EMAIL));
				obj.setId(rs.getInt(Userinfo.COLUMN_ID));
				obj.setName(rs.getString(Userinfo.COLUMN_NAME));
				obj.setPassAns(rs.getString(Userinfo.COLUMN_PASS_ANS));
				obj.setPassPro(rs.getString(Userinfo.COLUMN_PASS_PRO));
				obj.setState(rs.getInt(Userinfo.COLUMN_STATE));
				obj.setTelephone(rs.getString(Userinfo.COLUMN_TELEPHONE));
				obj.setUsername(rs.getString(Userinfo.COLUMN_USERNAME)); 
			    obj.setPassword(rs.getString(Userinfo.COLUMN_PASSWORD));
			    list.add(obj);
			}
			rs.close();
			ps.close();
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	public Userinfo getById(int id) {
		Userinfo obj = null;
		try {
			Session session = SessionBuilder.oracleSession();
			Connection conn = session.connection();
			String sql = "select * from " + Userinfo.TABLE_NAME + " where " + Userinfo.COLUMN_ID + " = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				obj = new Userinfo();
				obj.setAddtime(rs.getTimestamp(Userinfo.COLUMN_ADDTIME));
				obj.setCompany(rs.getString(Userinfo.COLUMN_COMPANY));
				obj.setEmail(rs.getString(Userinfo.COLUMN_EMAIL));
				obj.setId(rs.getInt(Userinfo.COLUMN_ID));
				obj.setName(rs.getString(Userinfo.COLUMN_NAME));
				obj.setPassAns(rs.getString(Userinfo.COLUMN_PASS_ANS));
				obj.setPassPro(rs.getString(Userinfo.COLUMN_PASS_PRO));
				obj.setState(rs.getInt(Userinfo.COLUMN_STATE));
				obj.setTelephone(rs.getString(Userinfo.COLUMN_TELEPHONE));
				obj.setUsername(rs.getString(Userinfo.COLUMN_USERNAME)); 
			    obj.setPassword(rs.getString(Userinfo.COLUMN_PASSWORD));
			}
			rs.close();
			ps.close();
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return obj;
	}
	
	public Userinfo getByUserName(String username) {
		Userinfo obj = null;
		try {
			Session session = SessionBuilder.oracleSession();
			Connection conn = session.connection();
			String sql = "select * from " + Userinfo.TABLE_NAME + " where " + Userinfo.COLUMN_USERNAME + " = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				obj = new Userinfo();
				obj.setAddtime(rs.getTimestamp(Userinfo.COLUMN_ADDTIME));
				obj.setCompany(rs.getString(Userinfo.COLUMN_COMPANY));
				obj.setEmail(rs.getString(Userinfo.COLUMN_EMAIL));
				obj.setId(rs.getInt(Userinfo.COLUMN_ID));
				obj.setName(rs.getString(Userinfo.COLUMN_NAME));
				obj.setPassAns(rs.getString(Userinfo.COLUMN_PASS_ANS));
				obj.setPassPro(rs.getString(Userinfo.COLUMN_PASS_PRO));
				obj.setState(rs.getInt(Userinfo.COLUMN_STATE));
				obj.setTelephone(rs.getString(Userinfo.COLUMN_TELEPHONE));
				obj.setUsername(rs.getString(Userinfo.COLUMN_USERNAME)); 
			    obj.setPassword(rs.getString(Userinfo.COLUMN_PASSWORD));
			}
			rs.close();
			ps.close();
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return obj;
	}
	
	
	public Userinfo getLoginUser(String username, String password) {
		Userinfo obj = null;
		try {
			Session session = SessionBuilder.oracleSession();
			Connection conn = session.connection();
			String sql = "select * from " + Userinfo.TABLE_NAME + " where " + Userinfo.COLUMN_USERNAME + " = ? AND " + Userinfo.COLUMN_PASSWORD + " = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, password);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				obj = new Userinfo();
				obj.setAddtime(rs.getTimestamp(Userinfo.COLUMN_ADDTIME));
				obj.setCompany(rs.getString(Userinfo.COLUMN_COMPANY));
				obj.setEmail(rs.getString(Userinfo.COLUMN_EMAIL));
				obj.setId(rs.getInt(Userinfo.COLUMN_ID));
				obj.setName(rs.getString(Userinfo.COLUMN_NAME));
				obj.setPassAns(rs.getString(Userinfo.COLUMN_PASS_ANS));
				obj.setPassPro(rs.getString(Userinfo.COLUMN_PASS_PRO));
				obj.setState(rs.getInt(Userinfo.COLUMN_STATE));
				obj.setTelephone(rs.getString(Userinfo.COLUMN_TELEPHONE));
				obj.setUsername(rs.getString(Userinfo.COLUMN_USERNAME)); 
			    obj.setPassword(rs.getString(Userinfo.COLUMN_PASSWORD));
			}
			rs.close();
			ps.close();
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return obj;
	}
 

	public void deleteUserinfo(int id) {
		try {
			Session session = SessionBuilder.oracleSession();
			String sql = "delete from " + Userinfo.TABLE_NAME + " where " + Userinfo.COLUMN_ID + " = ?";
			Connection conn = session.connection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.executeUpdate();
			ps.close();
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public int save(Userinfo userinfo) {
		UserinfoDao userinfoDao = new UserinfoDaoImpl();
		int id = 0;
		try {
			
			Session session = SessionBuilder.oracleSession();
			Connection conn = session.connection();
		
			PreparedStatement ps =null;
			ResultSet rs = null;
			if(userinfo.getId()==null||userinfo.getId()==0){
				 
					String sqlMax = " select max("+Userinfo.COLUMN_ID+")+1 from   "+Userinfo.TABLE_NAME;
					ps = conn.prepareStatement(sqlMax);
					rs = ps.executeQuery();
					
					while (rs.next())
						id = rs.getInt(1);
					userinfo.setId(id);
			} 
			String sql = "insert into "+Userinfo.TABLE_NAME+"(USERNAME ,  PASSWORD ,NAME,COMPANY, TELEPHONE,EMAIL ,"
            +"PASS_PRO,PASS_ANS  ,ADDTIME ,STATE , ID )"
				+"  values (?,?,?,?,?,?,?,?,sysdate,?,?)";
			ps = conn.prepareStatement(sql);
			
			ps.setString(1,userinfo.getUsername());
			ps.setString(2, userinfo.getPassword());
			ps.setString(3,userinfo.getName());
			ps.setString(4, userinfo.getCompany());
			ps.setString(5,userinfo.getTelephone());
			ps.setString(6,userinfo.getEmail());
			ps.setString(7,userinfo.getPassPro()); 
			ps.setString(8,userinfo.getPassAns()) ;
			ps.setInt(9,TranslateHelper.parseInteger(userinfo.getState())); 
			ps.setInt(10, userinfo.getId()); 
			 
			ps.executeUpdate(); 
			ps.close();
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return id;

	}
	public int update(Userinfo userinfo) {
		UserinfoDao userinfoDao = new UserinfoDaoImpl();
		int id = 0;
		try {
			
			Session session = SessionBuilder.oracleSession();
			Connection conn = session.connection(); 
			PreparedStatement ps =null;
			ResultSet rs = null;
			 
			String sql = "update "+Userinfo.TABLE_NAME+" set USERNAME=?,  PASSWORD=?" +
					" ,NAME=?,COMPANY=?, TELEPHONE=?,EMAIL =?,PASS_PRO=?,PASS_ANS=?  ,STATE=? where ID=? ";
			ps = conn.prepareStatement(sql);
			
			ps.setString(1,userinfo.getUsername());
			ps.setString(2, userinfo.getPassword());
			ps.setString(3,userinfo.getName());
			ps.setString(4, userinfo.getCompany());
			ps.setString(5,userinfo.getTelephone());
			ps.setString(6,userinfo.getEmail());
			ps.setString(7,userinfo.getPassPro()); 
			ps.setString(8,userinfo.getPassAns()) ;
			ps.setInt(9,TranslateHelper.parseInteger(userinfo.getState())); 
			ps.setInt(10, userinfo.getId()); 
			ps.executeUpdate(); 
			  
			ps.close();
			session.close();
			id=0;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return id;

	}

}

